﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Oracle.ManagedDataAccess.Client;

namespace BuildCode.Models
{
    public class DbHelper
    {
        private string strConnection;
        private string strDbProvider;

        private static DbHelper instance = null;
        private DbHelper() { }
        public DbHelper(string conn)
        {
            strConnection = conn;
        }
        public DbHelper(string conn, string provider)
        {
            strConnection = conn;
            strDbProvider = provider;
        }
        public static DbHelper GetInstance()
        {
            if (instance == null)
            {
                instance = new DbHelper();
            }
            return instance;
        }
        public void SetConnect(string conn, string provider)
        {
            strConnection = conn;
            strDbProvider = provider;
        }

        public string GetStatus()
        {
            System.Data.Common.DbProviderFactory fac = DbProviderFactories.GetFactory(strDbProvider);
            using (IDbConnection conn = fac.CreateConnection())
            {
                try
                {
                    conn.ConnectionString = strConnection;
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                        return "连接成功";
                    }
                    else return "连接失败";
                }
                catch (Exception ex)
                {
                    return "连接失败" + ex.Message;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }

        public DataTable GetTable(string sql)
        {
            //string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["SDPDB"].ConnectionString;
            //System.Data.Common.DbProviderFactory fac = DbProviderFactories.GetFactory("System.Data.OracleClient");
            if (strDbProvider == "System.Data.OracleClient")
            {
                //Oracle的sql用这个最稳定，用IDbConnection经常出现奇怪的sql语法错误提示
                DataTable dt = new DataTable();   //声明一个DataTable对象
                using (OracleConnection conn = new OracleConnection(strConnection))
                {
                    try
                    {
                        conn.Open();
                        OracleDataAdapter adapter = new OracleDataAdapter();
                        var cmd = conn.CreateCommand();
                        cmd.CommandText = sql;
                        adapter.SelectCommand = cmd;
                        adapter.FillSchema(dt, SchemaType.Source);
                        adapter.Fill(dt);
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        Console.Write(ex.Message);
                        return new DataTable();
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                        {
                            conn.Close();
                            conn.Dispose();
                        }
                    }
                }
            }
            else
            {
                System.Data.Common.DbProviderFactory fac = DbProviderFactories.GetFactory(strDbProvider);
                using (IDbConnection conn = fac.CreateConnection())
                {
                    conn.ConnectionString = strConnection;
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    conn.Open();
                    DbDataAdapter adapter = fac.CreateDataAdapter();
                    DataTable dt = new DataTable();
                    adapter.SelectCommand = (DbCommand)cmd;
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }
    }
}
